﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using WebApplication3;

namespace SignalRecharts.Models
{
    public class DataSerices
    {
        //每天接收任务，每天派发，每天接收任务未完成，每天派发未完成
        public string Everyday()
        {
            //每天接收任务
            string sql1 = "SELECT SUBSTRING(Dates,6,5) as Dates,SumNumberItems FROM ( ";
            sql1 += "SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY getdate())AS ID, ";
            sql1 += "substring(Convert(VARCHAR(30), SetTime, 120),1,10) as Dates ,sum(ItemsNumber) as SumNumberItems ";
            sql1 += "from Contract group by substring(Convert(VARCHAR(30), SetTime, 120),1,10) ORDER BY Dates DESC) AS A ORDER BY ID DESC";
            //每天派发
            string sql2 = "SELECT SUBSTRING(Dates,6,5) as Dates,SumNumberItems FROM ( ";
            sql2 += "SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY getdate())AS ID, ";
            sql2 += "substring(Convert(VARCHAR(30), LotNoDate, 120),1,10) as Dates ,sum(ItemsNumber) as SumNumberItems FROM Contract ";
            sql2 += "group by substring(Convert(VARCHAR(30),LotNoDate, 120),1,10)ORDER BY Dates DESC) AS A ORDER BY ID DESC";
            //每天接收任务未完成
            string sql3 = "SELECT SUBSTRING(Dates,6,5) as Dates,SumNumberItems FROM ( ";
            sql3 += "SELECT TOP 10 substring(Convert(VARCHAR(30), SetTime, 120),1,10) as Dates ,sum(ItemsNumber) as SumNumberItems FROM Contract ";
            sql3 += "WHERE PackageQtyStatus<5 group by substring(Convert(VARCHAR(30), SetTime, 120),1,10)) AS A ORDER BY Dates";
            //每天派发未完成
            string sql4 = "SELECT SUBSTRING(Dates,6,5) as Dates,SumNumberItems FROM ( ";
            sql4 += "SELECT TOP 10 substring(Convert(VARCHAR(30), LotNoDate, 120),1,10) as Dates ,sum(ItemsNumber) as SumNumberItems FROM Contract ";
            sql4 += "WHERE PackageQtyStatus<5 group by substring(Convert(VARCHAR(30), LotNoDate, 120),1,10)) AS A ORDER BY Dates";

            try
            {
                SqlDataReader reader1 = SqlHelper.GetReader(sql1);
                SqlDataReader reader2 = SqlHelper.GetReader(sql2);
                SqlDataReader reader3 = SqlHelper.GetReader(sql3);
                SqlDataReader reader4 = SqlHelper.GetReader(sql4);
                List<string> ALLdateList = new List<string>();
                DataTable dt1 = null;
                DataTable dt2 = null;
                DataTable dt3 = null;
                DataTable dt4 = null;
                Dictionary<string, int> zd1 = new Dictionary<string, int>();
                Dictionary<string, int> zd2 = new Dictionary<string, int>();
                Dictionary<string, int> zd3 = new Dictionary<string, int>();
                Dictionary<string, int> zd4 = new Dictionary<string, int>();
                if (reader1.HasRows)
                {
                    dt1 = new DataTable();
                    dt1.Load(reader1);
                }
                if (reader2.HasRows)
                {
                    dt2 = new DataTable();
                    dt2.Load(reader1);
                }
                if (reader3.HasRows)
                {
                    dt3 = new DataTable();
                    dt3.Load(reader3);
                }
                if (reader4.HasRows)
                {
                    dt4 = new DataTable();
                    dt4.Load(reader4);
                }
                //收集接收和派发中的所有日期
                if (dt1 != null)
                {
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        if (!ALLdateList.Contains(dt1.Rows[i]["Dates"].ToString()))
                        {
                            ALLdateList.Add(dt1.Rows[i]["Dates"].ToString());
                        }
                    }
                }
                if (dt2 != null)
                {
                    for (int i = 0; i < dt2.Rows.Count; i++)
                    {
                        if (!ALLdateList.Contains(dt2.Rows[i]["Dates"].ToString()))
                        {
                            ALLdateList.Add(dt2.Rows[i]["Dates"].ToString());
                        }
                    }
                }
                //对日期排序
                ALLdateList.Sort();
                //初始化字典
                for (int i = 0; i < ALLdateList.Count; i++)
                {
                    zd1[ALLdateList[i]] = 0;
                    zd2[ALLdateList[i]] = 0;
                    zd3[ALLdateList[i]] = 0;
                    zd4[ALLdateList[i]] = 0;
                }
                //
                if (dt1 !=null)
                {
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        zd1[dt1.Rows[i]["Dates"].ToString()] = Convert.ToInt32(dt1.Rows[i]["SumNumberItems"].ToString());
                    }
                }
                if (dt2 != null)
                {
                    for (int i = 0; i < dt2.Rows.Count; i++)
                    {
                        zd2[dt2.Rows[i]["Dates"].ToString()] = Convert.ToInt32(dt2.Rows[i]["SumNumberItems"].ToString());
                    }
                }
                if (dt3 != null)
                {
                    for (int i = 0; i < dt3.Rows.Count; i++)
                    {
                        zd3[dt3.Rows[i]["Dates"].ToString()] = Convert.ToInt32(dt3.Rows[i]["SumNumberItems"].ToString());
                    }
                }
                if (dt4 != null)
                {
                    for (int i = 0; i < dt4.Rows.Count; i++)
                    {
                        zd4[dt4.Rows[i]["Dates"].ToString()] = Convert.ToInt32(dt4.Rows[i]["SumNumberItems"].ToString());
                    }
                }

                List<Dictionary<string, int>> list = new List<Dictionary<string, int>>();
                list.Add(zd1);
                list.Add(zd2);
                list.Add(zd3);
                list.Add(zd4);
                string strjson = JsonConvert.SerializeObject(list);
                return strjson;
            }
            catch (Exception ex)
            {

                throw ex;
            } 
        }
        //未完成(待齐套,待签单,待包装,待出箱单,待通知)
        public string Unfinished()
        {
            string sql = "SELECT DAYS,PackageQtyStatus,Note,SUM(XS) AS XS FROM ( ";
            sql += "SELECT DAY(SetTime) AS DAYS,PackageQtyStatus,IIF(CHARINDEX('*',Note)=0,'','*') AS Note,SUM(ItemsNumber) AS XS FROM Contract ";
            sql += "WHERE PackageQtyStatus<4 GROUP BY DAY(SetTime),PackageQtyStatus,Note) AS A GROUP BY DAYS,PackageQtyStatus,Note order by DAYS";
            SqlDataReader reader = SqlHelper.GetReader(sql);
            if (!reader.HasRows)
            {
                return null;
            }
            DataTable dt = new DataTable();
            dt.Load(reader);
            Dictionary<string, int> zd0 = new Dictionary<string, int>();//待齐套
            Dictionary<string, int> zd1 = new Dictionary<string, int>();//待签单
            Dictionary<string, int> zd2 = new Dictionary<string, int>();//待包装
            Dictionary<string, int> zd3 = new Dictionary<string, int>();//待出箱单
            Dictionary<string, int> zd4 = new Dictionary<string, int>();//待通知
            string d = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                d = dt.Rows[i]["DAYS"].ToString();
                if (!zd0.ContainsKey(d))
                {
                    zd0[d] = 0;
                    zd1[d] = 0;
                    zd2[d] = 0;
                    zd3[d] = 0;
                    zd4[d] = 0;
                }
            }
            int x = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                x = Convert.ToInt32(dt.Rows[i]["XS"].ToString());
                if (dt.Rows[i]["Note"].ToString() == "*")
                {
                    zd4[dt.Rows[i]["DAYS"].ToString()] = x;
                }
                else
                {
                    switch (dt.Rows[i]["PackageQtyStatus"].ToString())
                    {
                        case "0":
                            zd0[dt.Rows[i]["DAYS"].ToString()] = x;
                            break;
                        case "1":
                            zd1[dt.Rows[i]["DAYS"].ToString()] = x;
                            break;
                        case "2":
                            zd2[dt.Rows[i]["DAYS"].ToString()] = x;
                            break;
                        case "3":
                            zd3[dt.Rows[i]["DAYS"].ToString()] = x;
                            break;
                    }
                }
            }
            List<Dictionary<string, int>> list = new List<Dictionary<string, int>>();
            list.Add(zd0);
            list.Add(zd1);
            list.Add(zd2);
            list.Add(zd3);
            list.Add(zd4);
            string strjson = JsonConvert.SerializeObject(list);
            return strjson;
        }
       
    }
}
